package by.scsbsu.database.dao;

import by.scsbsu.database.bean.StudyCourse;
import by.scsbsu.database.tools.DbConnectionPool;

public class StudyCourseDAO extends AbstractDAO {
	public static final String SQL_STUDY_COURSE_CREATE_STUDY_COURSE = "INSERT INTO study_course ( id_teacher , name , id_student_group , id_subject ) VALUES ( ? , ? , ? , ? );";
	public static final String SQL_STUDY_COURSE_GET_ID_CREATE_STUDY_COURSE = "SELECT id FROM study_course WHERE ( ( id_teacher = ? ) AND ( id_subject = ? ) );";
	public static final String SQL_STUDY_COURSE_UPDATE_NAME = "UPDATE study_course SET ( name = ? ) WHERE ( id = ? );";
	public static final String SQL_STUDY_COURSE_DELETE_STUDY_COURSE_BY_ID = "DELETE FROM study_course WHERE ( id = ? );";
	
	
	public StudyCourse createStudyCourse(StudyCourse studyCourse){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_STUDY_COURSE_CREATE_STUDY_COURSE);
			statement.setInt(1, studyCourse.getIdTeacher());
			statement.setString(2, studyCourse.getName());
			statement.setInt(3, studyCourse.getIdStudentGroup());
			statement.setInt(4, studyCourse.getIdSubject());
			statement.executeUpdate();
			
			statement = connection.prepareStatement(SQL_STUDY_COURSE_GET_ID_CREATE_STUDY_COURSE);
			statement.setInt(1, studyCourse.getIdTeacher());
			statement.setInt(2, studyCourse.getIdSubject());
			
			resultSet = statement.executeQuery();
			if(resultSet.next()){
				studyCourse.setId(resultSet.getInt("id"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return studyCourse;
	}
	
	public void updateStudyCourse(int id, String name){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_STUDY_COURSE_UPDATE_NAME);
			statement.setString(1, name);
			statement.setInt(2, id);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
	
	public void deleteStudyCourseById(int id){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_STUDY_COURSE_DELETE_STUDY_COURSE_BY_ID);
			statement.setInt(1, id);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
}
